﻿using System;
using System.Collections.Generic;
using System.Text;
using PPAP.Model;
using System.Data;
using System.Data.OracleClient;

namespace PPAP.DAO
{
    public class SamplecardDAO
    {
        public SamplecardDAO() { }
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Samplecard model)
        {
            model.ID = DbHelperOra.GetMaxID("ID", "SAMPLECARD");
            model.SAMPLECARDNO = GetSAMPLECARDNO(DbHelperOra.GetMaxID("SAMPLECARDNO", "SAMPLECARD"));
            model.Inspectionreportid = Guid.NewGuid().ToString();
            model.Issendmail = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into SAMPLECARD(");
            strSql.Append("ID,SAMPLECARDNO,VERSION,TESTDEPARTMENTID,DEPTREP,STARTTIME,ENDTIMEPLAN,ENDTIMEACTUAL,STATUS,RESULT,PPARID,REASON,INTERNALCOMMENT,INSPECTIONREPORTID,ISSENDMAIL)");
            strSql.Append(" values (");
            strSql.Append(":ID,:SAMPLECARDNO,:VERSION,:TESTDEPARTMENTID,:DEPTREP,:STARTTIME,:ENDTIMEPLAN,:ENDTIMEACTUAL,:STATUS,:RESULT,:PPARID,:REASON,:INTERNALCOMMENT,:INSPECTIONREPORTID,:ISSENDMAIL)");
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SAMPLECARDNO", OracleType.VarChar,50),
					new OracleParameter(":VERSION", OracleType.VarChar,10),
					new OracleParameter(":TESTDEPARTMENTID", OracleType.Number,4),
					new OracleParameter(":DEPTREP", OracleType.VarChar,50),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIMEPLAN", OracleType.DateTime),
					new OracleParameter(":ENDTIMEACTUAL", OracleType.DateTime),
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":RESULT", OracleType.VarChar,50),
					new OracleParameter(":PPARID", OracleType.Number,4),
                                           new OracleParameter(":REASON",OracleType.VarChar,200),
                                           new OracleParameter(":INTERNALCOMMENT",OracleType.VarChar,200),
                                           new OracleParameter(":INSPECTIONREPORTID",OracleType.VarChar,50),
                                           new OracleParameter(":ISSENDMAIL",OracleType.Number,4)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SAMPLECARDNO;
            parameters[2].Value = model.VERSION;
            parameters[3].Value = model.TESTDEPARTMENTID;
            parameters[4].Value = model.DEPTREP;
            parameters[5].Value = model.STARTTIME;
            parameters[6].Value = model.ENDTIMEPLAN;
            parameters[7].Value = model.ENDTIMEACTUAL;
            parameters[8].Value = model.STATUS;
            parameters[9].Value = model.RESULT;
            parameters[10].Value = model.PPARID;
            parameters[11].Value = model.Reason;
            parameters[12].Value = model.Internalcomment;
            parameters[13].Value = model.Inspectionreportid;
            parameters[14].Value = model.Issendmail;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters)>0?int.Parse(model.ID.ToString()):0;
         }

        /// <summary>
        /// 格式化SAMPLECARDNO
        /// </summary>
        /// <param name="MaxNo"></param>
        /// <returns></returns>
        private string GetSAMPLECARDNO(int MaxNo)
        {
            string NewNo = "";
            switch (MaxNo.ToString().Length)
            {
                case 1:
                    NewNo = "00000" + MaxNo;
                    break;
                case 2:
                    NewNo = "0000" + MaxNo;
                    break;
                case 3:
                    NewNo = "000" + MaxNo;
                    break;
                case 4:
                    NewNo = "00" + MaxNo;
                    break;
                case 5:
                    NewNo = "0" + MaxNo;
                    break;
                default:
                    NewNo = MaxNo.ToString();
                    break;
            }
            return NewNo;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(Samplecard model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SAMPLECARD set ");
            strSql.Append("ID=:ID,");
            strSql.Append("SAMPLECARDNO=:SAMPLECARDNO,");
            strSql.Append("VERSION=:VERSION,");
            strSql.Append("TESTDEPARTMENTID=:TESTDEPARTMENTID,");
            strSql.Append("DEPTREP=:DEPTREP,");
            strSql.Append("STARTTIME=:STARTTIME,");
            strSql.Append("ENDTIMEPLAN=:ENDTIMEPLAN,");
            strSql.Append("ENDTIMEACTUAL=:ENDTIMEACTUAL,");
            strSql.Append("STATUS=:STATUS,");
            strSql.Append("RESULT=:RESULT,");
            strSql.Append("PPARID=:PPARID,");
            strSql.Append("REASON=:REASON,");
            strSql.Append("INTERNALCOMMENT=:INTERNALCOMMENT,");
            strSql.Append("INSPECTIONREPORTID=:INSPECTIONREPORTID");
            strSql.Append(" where id="+model.ID);
            OracleParameter[] parameters = {
					new OracleParameter(":ID", OracleType.Number,4),
					new OracleParameter(":SAMPLECARDNO", OracleType.VarChar,50),
					new OracleParameter(":VERSION", OracleType.VarChar,10),
					new OracleParameter(":TESTDEPARTMENTID", OracleType.Number,4),
					new OracleParameter(":DEPTREP", OracleType.VarChar,50),
					new OracleParameter(":STARTTIME", OracleType.DateTime),
					new OracleParameter(":ENDTIMEPLAN", OracleType.DateTime),
					new OracleParameter(":ENDTIMEACTUAL", OracleType.DateTime),
					new OracleParameter(":STATUS", OracleType.Number,4),
					new OracleParameter(":RESULT", OracleType.VarChar,50),
					new OracleParameter(":PPARID", OracleType.Number,4),
                                           new OracleParameter(":REASON",OracleType.VarChar,200),
                                           new OracleParameter(":INTERNALCOMMENT",OracleType.VarChar,200),
                                           new OracleParameter(":INSPECTIONREPORTID",OracleType.VarChar,50)};
            parameters[0].Value = model.ID;
            parameters[1].Value = model.SAMPLECARDNO;
            parameters[2].Value = model.VERSION;
            parameters[3].Value = model.TESTDEPARTMENTID;
            parameters[4].Value = model.DEPTREP;
            parameters[5].Value = model.STARTTIME;
            parameters[6].Value = model.ENDTIMEPLAN;
            parameters[7].Value = model.ENDTIMEACTUAL;
            parameters[8].Value = model.STATUS;
            parameters[9].Value = model.RESULT;
            parameters[10].Value = model.PPARID;
            parameters[11].Value = model.Reason;
            parameters[12].Value = model.Internalcomment;
            parameters[13].Value = model.Inspectionreportid;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 重新分配
        /// </summary>
        public int UpdateDEPTREP(Samplecard model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update SAMPLECARD set ");
            strSql.Append("DEPTREP=:DEPTREP,");
            strSql.Append("ENDTIMEPLAN=:ENDTIMEPLAN");
            strSql.Append(" where PPARID=" + model.PPARID + " and TESTDEPARTMENTID="+model.TESTDEPARTMENTID);
            OracleParameter[] parameters = {
					new OracleParameter(":DEPTREP", OracleType.VarChar,50),
					new OracleParameter(":ENDTIMEPLAN", OracleType.DateTime)};
            parameters[0].Value = model.DEPTREP;
            parameters[1].Value = model.ENDTIMEPLAN;

            return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from SAMPLECARD ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

           return DbHelperOra.ExecuteSql(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Samplecard GetModel(int id)
        {
            //该表无主键信息，请自定义主键/条件字段
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,SAMPLECARDNO,VERSION,TESTDEPARTMENTID,DEPTREP,STARTTIME,ENDTIMEPLAN,ENDTIMEACTUAL,STATUS,RESULT,PPARID,REASON,INTERNALCOMMENT,INSPECTIONREPORTID from SAMPLECARD ");
            strSql.Append(" where id="+id);
            OracleParameter[] parameters = {
};

            Samplecard model = new Samplecard();
            DataSet ds = DbHelperOra.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                model.SAMPLECARDNO = ds.Tables[0].Rows[0]["SAMPLECARDNO"].ToString();
                model.VERSION = ds.Tables[0].Rows[0]["VERSION"].ToString();
                if (ds.Tables[0].Rows[0]["TESTDEPARTMENTID"].ToString() != "")
                {
                    model.TESTDEPARTMENTID = int.Parse(ds.Tables[0].Rows[0]["TESTDEPARTMENTID"].ToString());
                }
                model.DEPTREP = ds.Tables[0].Rows[0]["DEPTREP"].ToString();
                if (ds.Tables[0].Rows[0]["STARTTIME"].ToString() != "")
                {
                    model.STARTTIME = DateTime.Parse(ds.Tables[0].Rows[0]["STARTTIME"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ENDTIMEPLAN"].ToString() != "")
                {
                    model.ENDTIMEPLAN = DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIMEPLAN"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ENDTIMEACTUAL"].ToString() != "")
                {
                    model.ENDTIMEACTUAL = DateTime.Parse(ds.Tables[0].Rows[0]["ENDTIMEACTUAL"].ToString());
                }
                if (ds.Tables[0].Rows[0]["STATUS"].ToString() != "")
                {
                    model.STATUS = int.Parse(ds.Tables[0].Rows[0]["STATUS"].ToString());
                }
                model.RESULT = ds.Tables[0].Rows[0]["RESULT"].ToString();
                if (ds.Tables[0].Rows[0]["PPARID"].ToString() != "")
                {
                    model.PPARID = int.Parse(ds.Tables[0].Rows[0]["PPARID"].ToString());
                }
                model.Reason = ds.Tables[0].Rows[0]["Reason"].ToString();
                model.Internalcomment = ds.Tables[0].Rows[0]["Internalcomment"].ToString();
                model.Inspectionreportid = ds.Tables[0].Rows[0]["Inspectionreportid"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.*,rownum from (select s.ID,SAMPLECARDNO,VERSION,TESTDEPARTMENTID,DEPTREP,STARTTIME,ENDTIMEPLAN,ENDTIMEACTUAL,s.STATUS,RESULT,PPARID,t.testitemname,'PPAR-'||a.pparno||'.'||a.PPARVERSION as pparno,s.reason,r.bdntpartname,su.suppliername,su.suppliercode,r.bdntpartno,r.color from samplecard s left join testdepartment t on s.testdepartmentid=t.id left join ppar a on s.pparid=a.id left join users u on s.deptrep=u.username             left join part r on r.id=a.partid           left join supplier su on su.id=r.supplierid   ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by SAMPLECARDNO)t");
            return DbHelperOra.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取测试人员任务 包括权限代理任务
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public DataSet GetListForUser(string user)
        {
            string sql=string.Format(@"
            select rownum,t.* from (select s.ID,SAMPLECARDNO,VERSION,TESTDEPARTMENTID,DEPTREP,STARTTIME,ENDTIMEPLAN,ENDTIMEACTUAL,s.STATUS,RESULT,PPARID,
            t.testitemname,'PPAR-'||a.pparno||'.'||a.PPARVERSION as pparno,u.username,u.substitutesuser,u.substitutesdate,r.bdntpartname,su.suppliername,su.suppliercode,r.bdntpartno,r.color from samplecard s left join testdepartment t on 
            s.testdepartmentid=t.id left join ppar a on s.pparid=a.id  
            left join SUBSTITUTES u on s.deptrep=u.username  and u.substitutesenable=1  and u.substitutesdate>=sysdate --{0}
            left join part r on r.id=a.partid
            left join supplier su on su.id=r.supplierid            
            where  ( s.status=0 or s.status=2)  order by SAMPLECARDNO )t
            where deptrep='{1}' or substitutesuser='{1}' ", "2012-10-29",user);
            return DbHelperOra.Query(sql);
        }

       

        /// <summary>
        /// 根据PPARid查询Sample状态 不包括已终止的card
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public DataSet GetSampleResult(string pparid)
        {
            string sql = "select testitemname,result,s.status from samplecard s left join testdepartment t on s.testdepartmentid=t.id where s.status!=9 and pparid=" + pparid;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public DataSet GetSampleForSearch(string sqlwhere)
        {
            string sql = @"select rownum,t.* from (select 'PPAR-'||a.pparno||'.'||PPARVERSION as pparno,r.bdntpartno,l.SupplierCode,s.samplecardno,t.testitemname,s.deptrep,'' ShowStatus,
                                            s.id,s.result,s.starttime,s.endtimeplan,'' d1,'' d2,s.status,a.id as rid
                          from samplecard s left join testdepartment t on s.testdepartmentid=t.id
                            left join ppar a on s.pparid=a.id
                            left join part r on a.partid=r.id
                            left join supplier l on r.supplierid=l.id where 1=1  ";
            sql += sqlwhere;
            sql += " order by samplecardno )t";

            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 用于判断该PPAR是否有未检测完成的样品  不包括已终止的card
        /// </summary>
        /// <param name="pparid"></param>
        /// <returns></returns>
        public int GetNoPassSample(string pparid)
        {
            string sql = "select * from samplecard where pparid="+pparid+" and status<>3 and status<>9";
            return DbHelperOra.Query(sql).Tables[0].Rows.Count;
        }

        /// <summary>
        /// Print
        /// </summary>
        /// <param name="SampleCardId"></param>
        /// <returns></returns>
        public DataSet GetPrint(string SampleCardId)
        {
            string sql = @"select m.samplecardno,m.version,a.pparno,a.pparversion,m.status,s.suppliername,r.bdntrevisionno,r.supplierpartno,r.bdntrevisionstatus,
                            r.reasonforsampling,r.modificationscope,m.result,t.testitemname,r.ecuhwversionno,r.ecuswversionno,a.quanlitymanager,m.starttime,m.deptrep,u1.mobilephone mo1,u2.mobilephone mo2,s.suppliercode  from samplecard m left join ppar a on m.pparid=a.id
                            left join part r on a.partid=r.id
                            left join supplier s on r.supplierid=s.id
                            left join testdepartment t on m.testdepartmentid=t.id
                            left join users u1 on a.quanlitymanager=u1.username
                            left join users u2 on m.deptrep=u2.username
                            where m.id=" + SampleCardId;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 获取SampleCardName
        /// </summary>
        /// <param name="SampleCardId"></param>
        /// <returns></returns>
        public DataSet GetSampleCardName(int? SampleCardId)
        {
            string sql = "select 'SC-'||s.samplecardno as samplecardno,t.testitemname from samplecard s left join testdepartment t on s.testdepartmentid=t.id where s.id="+SampleCardId;
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 判断一个PPAR下的CARD是否全部检测结束
        /// </summary>
        /// <param name="samplecardid"></param>
        /// <returns></returns>
        public bool CheckSampleCardPass(int samplecardid)
        {
            string sql=string.Format(@" select * from samplecard where pparid=(
                        select pparid from samplecard where id={0})
                        and status!=3",samplecardid);
            return DbHelperOra.Query(sql).Tables[0].Rows.Count == 0;
        }

    }
}
